package com.emuporium.gymrat.android.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class DatabaseExerciseAdapter {
	private SQLiteDatabase db = null;
	private Context context;

	public DatabaseExerciseAdapter(Context databaseContext) {
		context = databaseContext;

		// create/open stuff here
	}

	/**
	 * Adds one type of equipment to the exercise.
	 * 
	 * @param exerciseId
	 *            id of exercise to add to
	 * @param equipmentId
	 *            id of equipment to be added.
	 * @return true if successful, false if not.
	 */
	public boolean addEquipmentToExercise(int exerciseId, int equipmentId) {
		ContentValues cv = new ContentValues();
		cv.put("exerciseid", exerciseId);
		cv.put("equipmentid", equipmentId);
		if (db.insert("exerciseequipment", "_id", cv) > 0)
			return true;
		else
			return false;
	}

	/**
	 * Adds one variation to the exercise.
	 * 
	 * @param exerciseId
	 *            id of exercise to add to
	 * @param variationId
	 *            id of variation to be added.
	 * @return true if successful, false if not.
	 */
	public boolean addVariationToExercise(int exerciseId, int variationId) {
		ContentValues cv = new ContentValues();
		cv.put("exerciseid", exerciseId);
		cv.put("variationid", variationId);
		if (db.insert("exercisevariations", "_id", cv) > 0)
			return true;
		else
			return false;
	}

	/**
	 * Change the muscle group associated with an exercise
	 * 
	 * @param exerciseId
	 *            id of exercise to be modified
	 * @param muscleGroupId
	 *            id of new muscle group
	 * @return true if successful, false otherwise (bad group id?)
	 */
	public boolean changeMuscleGroup(int exerciseId, int muscleGroupId) {
		ContentValues values = new ContentValues();
		values.put("musclegroup", muscleGroupId);
		if (db.update("exercises", values, "_id=?", new String[] { ""
				+ exerciseId }) > 0)
			return true;
		else
			return false;

	}

	/**
	 * Close the database associated with this adapter.
	 */
	public void close() {
		if (db.isOpen())
			db.close();
	}

	/**
	 * Get all exercises in the database.
	 * 
	 * @return Cursor containing all of the exercises with the following
	 *         columns, ordered by muscle group then name<br>
	 *         0 <b>_id</b><br>
	 *         1 <b>name</b><br>
	 *         2 <b>group</b><br>
	 *         3 <b>isCustom</b>
	 */
	public Cursor getAll() {
		return db
				.rawQuery(
						"select exercises._id, exercises.name, musclegroups.name AS \'musclegroup\', isCustom from exercises\r\n"
								+ "\r\n"
								+ "join musclegroups on exercises.musclegroup = musclegroups._id "
								+ "order by musclegroups.name, exercises.name",
						null);
	}

	/**
	 * Get one specific exercise
	 * 
	 * @param id
	 *            id of exercise to fetch
	 * @return Cursor containing information on the one exercise with the
	 *         following columns: <br>
	 *         0 <b>_id</b><br>
	 *         1 <b>name</b><br>
	 *         2 <b>group</b><br>
	 *         3 <b>isCustom</b>
	 */
	public Cursor getExercise(int id) {
		return db
				.rawQuery(
						"select exercises._id, exercises.name, musclegroups.name AS \'musclegroup\', exercises.isCustom from exercises "
								+ "join musclegroups on exercises.musclegroup = musclegroups._id "
								+ "where exercises._id = ?",
						new String[] { Integer.toString(id) });
	}

	/**
	 * Get all equipment associated with a particular exercise
	 * 
	 * @param id
	 *            id of exercise to find equipment for
	 * @return Cursor containing information on the equipment in the following
	 *         columns:<br>
	 *         0 <b>_id</b><br>
	 *         1 <b>name</b><br>
	 */
	public Cursor getExerciseEquipment(int id) {
		return db
				.rawQuery(
						"select equipment._id, equipment.name from equipment\r\n"
								+ "join exerciseequipment on (equipment._id = exerciseequipment.equipmentid)\r\n"
								+ "join exercises on (exercises._id = exerciseequipment.exerciseid)\r\n"
								+ "where exercises._id=? order by equipment.name",
						new String[] { Integer.toString(id) });
	}

	/**
	 * Get all variations associated with a particular exercise
	 * 
	 * @param id
	 *            id of exercise to find variations for
	 * @return Cursor containing information on the variations in the following
	 *         columns:<br>
	 *         0 <b>_id</b><br>
	 *         1 <b>name</b><br>
	 */
	public Cursor getExerciseVariations(int id) {
		return db
				.rawQuery(
						"select variations._id, variations.name from variations\r\n"
								+ "join exercisevariations on (variations._id = exercisevariations.variationid)\r\n"
								+ "join exercises on (exercises._id = exercisevariations.exerciseid)\r\n"
								+ "where exercises._id=? order by variations.name",
						new String[] { Integer.toString(id) });
	}

	/**
	 * Get all muscle groups
	 * 
	 * @return Cursor containing information on the muscle groups in the
	 *         following columns:<br>
	 *         0 <b>_id</b><br>
	 *         1 <b>name</b><br>
	 */
	public Cursor getMuscleGroups() {
		return db.rawQuery(
				"select _id, name from musclegroups order by location asc",
				null);
	}

	/**
	 * Get all equipment not currently associated with a particular exercise
	 * 
	 * @param exerciseId
	 *            id of exercise to find unused equipment for
	 * @return Cursor containing information on the equipment in the following
	 *         columns:<br>
	 *         0 <b>_id</b><br>
	 *         1 <b>name</b><br>
	 */
	public Cursor getUnusedEquipment(int exerciseId) {
		return db.rawQuery("select _id, name from equipment\r\n"
				+ "where equipment._id \r\n" + "not in \r\n"
				+ "(select exerciseequipment.equipmentid \r\n"
				+ "from exerciseequipment \r\n"
				+ "where exerciseequipment.exerciseid=?) \r\n"
				+ "order by equipment.name", new String[] { Integer
				.toString(exerciseId) });
	}

	/**
	 * Get all variations not currently associated with a particular exercise
	 * 
	 * @param exerciseId
	 *            id of exercise to find unused variations for
	 * @return Cursor containing information on the variations in the following
	 *         columns:<br>
	 *         0 <b>_id</b><br>
	 *         1 <b>name</b><br>
	 */
	public Cursor getUnusedVariations(int exerciseId) {
		return db.rawQuery("select _id, name from variations\r\n"
				+ "where variations._id \r\n" + "not in \r\n"
				+ "(select exercisevariations.variationid \r\n"
				+ "from exercisevariations \r\n"
				+ "where exercisevariations.exerciseid=?) \r\n"
				+ "order by variations.name", new String[] { Integer
				.toString(exerciseId) });
	}

	public DatabaseExerciseAdapter open() throws SQLException {
		// helper = new DatabaseHelper(context);
		if (db == null || !db.isOpen())
			db = DatabaseHelper.getStaticDb();
		return this;
	}

	/**
	 * Remove a equipment from the chosen exercise
	 * 
	 * @param exerciseId
	 *            id of exercise to remove the variation from
	 * @param equipmentId
	 *            id of equipment to be removed from exercise
	 * @return true if successful, false if the equipment never was associated
	 *         with the exercise
	 */
	public boolean removeEquipmentFromExercise(int exerciseId, int equipmentId) {
		if (db.delete("exerciseequipment", "exerciseid = ? and equipmentid=?",
				new String[] { "" + exerciseId, "" + equipmentId }) > 0)
			return true;
		else
			return false;
	}

	/**
	 * Remove a variation from the chosen exercise
	 * 
	 * @param exerciseId
	 *            id of exercise to remove the variation from
	 * @param variationId
	 *            id of variation to be removed from exercise
	 * @return true if successful, false if the variation never was associated
	 *         with the exercise
	 */
	public boolean removeVariationFromExercise(int exerciseId, int variationId) {
		if (db.delete("exercisevariations", "exerciseid = ? and variationid=?",
				new String[] { "" + exerciseId, "" + variationId }) > 0)
			return true;
		else
			return false;
	}

	public boolean renameExercise(int exerciseId, String newName) {
		ContentValues cv = new ContentValues();
		cv.put("name", newName);
		if (db.update("exercises", cv, "_id = ?", new String[] { Integer
				.toString(exerciseId) }) > 0)
			return true;
		else
			return false;
	}
}
